package jp.bsws.dao;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.ArrayList;
import java.util.List;

import jp.bsws.bean.KeyValueBean;
import jp.bsws.bean.SyohizeiBean;
import jp.bsws.common.BSWSSession;
import jp.bsws.common.BSWSUtils;
import jp.bsws.model.SyohizeiModel;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class SyohizeiDao {
	private static SyohizeiDao syohizeiDao = new SyohizeiDao();

	/** ログ */
	private Log log = LogFactory.getLog(SyohizeiDao.class);

	/**
	 * コンストラクタ
	 */
	// Singletonパターンで単一のインスタンスを取得
	public static SyohizeiDao getInstance(){
		return syohizeiDao;
	}

	private String SQL_SYOHIZEI = " SELECT * FROM M_SYOHIZEI ";

	/**
	 * 消費税情報取得
	 * 引数のPKがあれば条件とする。
	 */
	public List<SyohizeiModel> getSyohizeiJoho(Connection con, Integer pkSeq, String pkGroupCod, String pkTenpoCod) throws Exception{

		List<SyohizeiModel> resultList = new ArrayList<SyohizeiModel>();

		//検索条件の作成
		String whereSeq = "";
		if( pkSeq != null ){
			whereSeq = BSWSUtils.getWhereAnd(whereSeq) + " SEQ = ? ";
		}

		String where = "";
		ArrayList<String> whereList = new ArrayList<String>();
		if( BSWSUtils.isNotBlankNull(pkGroupCod)){
			where += BSWSUtils.getWhereAnd(where) + " GROUP_COD = ? ";
			whereList.add(pkGroupCod);
		}
		if( BSWSUtils.isNotBlankNull(pkTenpoCod)){
			where += BSWSUtils.getWhereAnd(where) + " TENPO_COD = ? ";
			whereList.add(pkTenpoCod);
		}

		PreparedStatement stmt = con.prepareStatement(SQL_SYOHIZEI + where + whereSeq);
		int seqCnt = 0;
		if(BSWSUtils.isNotBlankNull(whereSeq)){
			stmt.setInt(1, pkSeq);
			seqCnt++;
		}
		for(int i = 0; i < whereList.size(); i++){
			stmt.setString(i + seqCnt + 1, (String)whereList.get(i));
		}
		ResultSet rs = stmt.executeQuery();

		SyohizeiModel syohizeiModel = null;
		while(rs.next()){
			syohizeiModel = new SyohizeiModel();
			syohizeiModel.setSeq( rs.getInt("SEQ"));
			syohizeiModel.setStrGroupCod(rs.getString("GROUP_COD"));
			syohizeiModel.setBdSyohizeiNum(rs.getBigDecimal("SYOHIZEI_NUM"));
			syohizeiModel.setIntKaishiYmd(rs.getInt("KAISHI_YMD"));
			syohizeiModel.setIntSyuryoYmd(rs.getInt("SYURYO_YMD"));
			resultList.add(syohizeiModel);
		}

		rs.close();
		stmt.close();

		return resultList;
	}

	private String SQL_SYOHIZEI_INSERT =
		" INSERT INTO " +
		"     M_SYOHIZEI ( " +
		"         SEQ " +
		"        ,GROUP_COD " +
		"        ,SYOHIZEI_NUM " +
		"        ,KAISHI_YMD " +
		"        ,SYURYO_YMD " +
		"        ,TOROKU_TMS " +
		"        ,TOROKUSHA_COD " +
		"        ,KOSHIN_TMS " +
		"        ,KOSHINSHA_COD " +
		"     ) VALUES ( " +
		"        ? " +
		"       ,? " +
		"       ,? " +
		"       ,? " +
		"       ,? " +
		"       ,now() " +
		"       ,? " +
		"       ,now() " +
		"       ,? " +
		"    ) ";

	/**
	 * 消費税情報追加
	 */
	public int insertSyohizei(Connection con, Integer seq, BSWSSession bSWSSession , SyohizeiBean syohizeiBean) throws Exception{

		BSWSDao bSWSDao = BSWSDao.getInstance();
		//最大値を取得
		Integer maxSeq = bSWSDao.getMaxSeq(con, "M_SYOHIZEI");
		if(seq == null){
			seq = maxSeq + 1;
		}

		PreparedStatement stmt = con.prepareStatement(SQL_SYOHIZEI_INSERT);
		stmt.setInt(1, seq);
		stmt.setString(2, bSWSSession.getStrGroupCod());
		stmt.setBigDecimal(5, new BigDecimal(Double.parseDouble(syohizeiBean.getStrSyohizeiNum()) / 100));
		stmt.setInt(6, Integer.parseInt(syohizeiBean.getStrKaishiYmd()));
		stmt.setInt(7, Integer.parseInt(syohizeiBean.getStrSyuryoYmd()));
		stmt.setString(8, bSWSSession.getStrJugyoinCod());
		stmt.setString(9, bSWSSession.getStrJugyoinCod());

		int cnt = stmt.executeUpdate();

		stmt.close();

		return cnt;
	}

	private String SQL_SYOHIZEI_UPDATE =
		" UPDATE " +
		"     M_SYOHIZEI " +
		" SET " +
		"         SYOHIZEI_NUM = ? " +
		"        ,KAISHI_YMD = ? " +
		"        ,SYURYO_YMD = ? " +
		"        ,KOSHIN_TMS = now() " +
		"        ,KOSHINSHA_COD = ? " +
		" WHERE " +
		"     SEQ = ? ";

	/**
	 * 消費税情報更新
	 */
	public int updateSyohizei(Connection con,BSWSSession bSWSSession , SyohizeiBean syohizeiBean) throws Exception{

		PreparedStatement stmt = con.prepareStatement(SQL_SYOHIZEI_UPDATE);

		stmt.setBigDecimal(1, new BigDecimal(Double.parseDouble(syohizeiBean.getStrSyohizeiNum()) / 100));
		stmt.setInt(2, Integer.parseInt(syohizeiBean.getStrKaishiYmd()));
		stmt.setInt(3, Integer.parseInt(syohizeiBean.getStrSyuryoYmd()));
		stmt.setString(4, bSWSSession.getStrJugyoinCod());
		stmt.setInt(5, syohizeiBean.getSeq());

		int cnt = stmt.executeUpdate();

		stmt.close();

		return cnt;
	}

	private String SQL_KV = " SELECT SEQ,SYOHIZEI_NUM FROM M_SYOHIZEI WHERE GROUP_COD = ? ";

	/**
	 * 店舗に登録されている消費税情報取得
	 * 引数のPKがあれば条件とする。
	 */
	public List<KeyValueBean> getSyohizeiList(Connection con, String pkGroupCod, String pkTenpoCod) throws Exception{
		List<KeyValueBean> resultList = new ArrayList<KeyValueBean>();

		//検索条件の作成
		String where = "";
		if( BSWSUtils.isNotBlankNull(pkTenpoCod)){
			where += " AND TENPO_COD = ? ";
		}

		PreparedStatement stmt = con.prepareStatement(SQL_KV + where);

		stmt.setString(1, pkGroupCod);
		if( BSWSUtils.isNotBlankNull(pkTenpoCod)){
			stmt.setString(2, pkTenpoCod);
		}
		ResultSet rs = stmt.executeQuery();

		KeyValueBean kvBean = null;
		while(rs.next()){
			 kvBean = new KeyValueBean();
			String key = rs.getString("SEQ");
			String value = rs.getString("SYOHIZEI_NUM");
			kvBean.setKey(key);
			kvBean.setValue(value);
			resultList.add(kvBean);
		}
		rs.close();
		stmt.close();

		return resultList;
	}
}